clear all
set type double

tempfile tempf_bid tempf_contract tempf_location tempf_buyer_location


import delimited using ../data/georgia/bidtab_firm043020.csv, clear encoding("utf8")   /*bindquotes(nobind) */
replace app_id = app_id[_n - 1] if app_id == . 
replace j = j[_n - 1] if j == .
replace time_obtained = time_obtained[_n - 1] if time_obtained == "" 

*destring v7, replace ignore(",")
*br if ustrregexm(v7, "[^0-9]+")
replace lastofferamountdate = usubinstr(lastofferamountdate, "`", ",",.)
replace firstofferamountdate = usubinstr(firstofferamountdate, "`", ",",.)

/* */
foreach t in "last" "first"{
	gen bid_`t' = ustrregexs(1) ///
		if ustrregexm(`t'offeramountdate, "([0-9,]+\.[0-9]+) ([0-9.:]+) ([0-9]+:[0-9]+)") == 1
	destring bid_`t', replace ignore(",")
	cap drop tempv
	gen tempv = ustrregexs(2) ///
		if ustrregexm(`t'offeramountdate, "([0-9,]+\.[0-9]+) ([0-9.:]+) ([0-9]+:[0-9]+)") == 1
	cap drop dy_bid_`t'
	gen dy_bid_`t' = date(tempv, "DMY")
	drop tempv
	gen tm_bid_`t' = ustrregexs(3) ///
		if ustrregexm(`t'offeramountdate, "([0-9,]+\.[0-9]+) ([0-9.:]+) ([0-9]+:[0-9]+)") == 1
}
format dy_bid* %td

*egen firm_id = group(bidder)
egen firm_id = group(js_showprofile_number_firm)
rename app_id contract_id

sort contract_id bid_last
order contract_id firm_id *_first *_last 
*drop offers lastofferamount lastofferamountdate v1


order contract_id firm_id *_first *_last


format bidder %30s
gen address_full = address + ", " + citytownvillage + ", " + country
compress
recast str440 bidder

save `tempf_bid'

preserve
	import delimited using ../data/georgia/contract031320.csv, clear encoding("utf8")
	*cap noisily rename i contract_id
	cap noisily rename app_id contract_id
	drop if announcmentnumber == ""
	count if contract_id == .
	save `tempf_contract'
	*use contract.dta, clear
restore


preserve
	import delimited using ../data/georgia/location_GoogleMapsT.csv, clear encoding("utf8")
	keep v2-v5
	drop if v4 == ""
	sort v2 v3 v4
	rename v2 latitude
	rename v3 longitude
	rename v4 address_google
	rename v5 address_full
	duplicates report
	duplicates drop
	recast str440 address_full
	compress
	save `tempf_location'
	*save location_GoogleMapsT.dta, replace
	*use location_GoogleMapsT.dta, clear
restore


preserve
	import delimited using ../data/georgia/buyer_location_GoogleMapsT.csv, clear encoding("utf8")
	drop v1
	drop if _n == 1
	rename v2 p0_latitude
	rename v3 p0_longitude
	rename v4 p0_address_google
	rename v5 p0_address_full
	recast str440 p0_address_full

	duplicates report
	duplicates drop
	save `tempf_buyer_location', replace
	*save buyer_location_GoogleMapsT.dta, replace
restore
	
	
******************************************
set type double
use `tempf_bid', clear
*br if js == 2212
replace address_full = ustrregexs(1) if ustrregexm(address_full, "^, (.+)")
format %100s address_full
*br address_full
*tab country


merge m:1 contract_id using `tempf_contract'
drop _merge
gen NAT = ustrregexm(announcmentnumber, "NAT")

* bidder geocode 
merge m:1 address_full using `tempf_location'
tab _merge if NAT == 1
rename _merge _merge_geobidder

* buyer geocode
gen p0_address_full = buyer_address + ", " + buyer_citytownvillage + ", " + buyer_country
merge m:1 p0_address_full using `tempf_buyer_location'
rename _merge _merge_geobuyer
tab _merge_geobuyer if NAT == 1 & p0_address_full != ""

*br if js == 2212

foreach v in "estimatedvalueofprocurement" "estimatedvalueofthepricelist"{
    replace `v' = usubinstr(`v', " GEL", "", .)
	replace `v' = usubinstr(`v', "`", "", .)
	destring `v', replace
}
order estimatedvalueofthepricelist bid_first bid_last

gen est = estimatedvalueofthepricelist
replace est = estimatedvalueofprocurement if est == .


/* added on 6jun22 to drop obs without bidder; possibly auctions in which no bids are received*/
drop if js_showprofile_number_firm == . /* drop auctions without any bidder*/


bys firm_id: gen N_participations = _N
bys contract_id: gen N_bids = _N
egen firm_tg = tag(firm_id)


/* bid ranking*/
set seed 5555

foreach v in "first" "last"{
	sort contract_id bid_`v' tm_bid_`v'
	by contract_id: gen bid_rank_`v' = _n if bid_`v' < .
	cap drop temp 
	g temp = bid_`v' if bid_rank_`v' == 1
	by contract_id: egen lbid_price_`v' = max(temp) 
	drop temp 
	
	forvalues x = 1(1)4{
		cap drop temp
		gen temp = bid_`v' if bid_rank_`v' == `x'
		bys contract_id: egen bid_`v'`x' = max(temp)
	}
	by contract_id: gen delta_`v' = (bid_`v' - bid_`v'1)/bid_`v' if bid_rank_`v' > 1 
	by contract_id: replace delta_`v' = (bid_`v' - bid_`v'2)/bid_`v' if bid_rank_`v' == 1
	/* for placebo*/
	by contract_id: gen delta23_`v' = (bid_`v' - bid_`v'2)/bid_`v' if bid_rank_`v' > 2 
	by contract_id: replace delta23_`v' = (bid_`v' - bid_`v'3)/bid_`v' if bid_rank_`v' == 2
	
	by contract_id: gen pdelta_`v' = (bid_`v' - bid_`v'1)/est if bid_rank_`v' > 1 
	by contract_id: replace pdelta_`v' = (bid_`v' - bid_`v'2)/est if bid_rank_`v' == 1
	/* for placebo*/
	by contract_id: gen pdelta23_`v' = (bid_`v' - bid_`v'2)/est if bid_rank_`v' >2
	by contract_id: replace pdelta23_`v' = (bid_`v' - bid_`v'3)/est if bid_rank_`v' == 2
	
	by contract_id: gen ldelta_`v' = (bid_`v' - bid_`v'1)/lbid_price_`v' if bid_rank_`v' > 1 
	by contract_id: replace ldelta_`v' = (bid_`v' - bid_`v'2)/lbid_price_`v' if bid_rank_`v' == 1
	/* for placebo*/
	by contract_id: gen ldelta23_`v' = (bid_`v' - bid_`v'2)/lbid_price_`v' if bid_rank_`v' > 2 
	by contract_id: replace ldelta23_`v' = (bid_`v' - bid_`v'3)/lbid_price_`v' if bid_rank_`v' == 2
}

cap drop winner
gen winner = (profilenum_awarder == js_showprofile_number_firm) if profilenum_awarder < .
replace winner = 0 if winner == .
bys contract_id: egen N_winners = total(winner)

cap drop temp
gen temp = bid_last if winner == 1
by contract_id: egen winbid = max(temp)

cap drop temp
gen pct_bid_first = bid_first / est
gen pct_bid_last = bid_last / est
gen temp = pct_bid_last if winner == 1
bys contract_id: egen pct_winbid = max(temp)
drop temp
gen pct_lowestbid = bid_last1 / est

/* delta for auctions in which the winner is not the lowest bidder*/
foreach v in "first" "last"{
	sort contract_id bid_`v' tm_bid_`v'
	cap drop gtwinner_`v'
	gen gtwinner_`v' = winner /* 1 for bidders whose bid is highter than the winner's*/
	by contract_id: replace gtwinner_`v' = gtwinner_`v'[_n-1] if _n > 1 & gtwinner_`v'[_n-1] > 0
	cap drop bidw_`v'
	gen bidw_`v' = bid_`v' if gtwinner_`v' == 1
	cap drop bidw_rank_`v'
	sort contract_id bidw_`v' tm_bid_`v'/*added 11mar22 w/o sorting bidw_`v' = dot comes first, and bidw_rank = 1 may be missing */
	by contract_id: gen bidw_rank_`v' = _n if bidw_`v' < . /* rankw = . for bidders whose bid is lower than winner*/
	/*highest losing firm whose bid is below winner's on 12mar22 to incorporate */
	sort contract_id bid_`v' tm_bid_`v'
	replace bidw_rank_`v' = 0 if bidw_rank_`v'[_n+1] == 1 & contract_id == contract_id[_n+1]
	replace bidw_`v' = bid_`v' if bidw_rank_`v' == 0

	
	forvalues x = 0(1)4{
		cap drop temp
		gen temp = bidw_`v' if bidw_rank_`v' == `x'
		bys contract_id: egen bidw_`v'`x' = max(temp)
		
	}
	cap drop temp
	by contract_id: gen deltaw_`v' = (bidw_`v' - bidw_`v'1)/bidw_`v' if bidw_rank_`v' > 1 
	by contract_id: replace deltaw_`v' = (bidw_`v' - bidw_`v'2)/bidw_`v' if bidw_rank_`v' == 1
	/* for placebo*/
	by contract_id: gen deltaw23_`v' = (bidw_`v' - bidw_`v'2)/bidw_`v' if bidw_rank_`v' > 2 
	by contract_id: replace deltaw23_`v' = (bidw_`v' - bidw_`v'3)/bidw_`v' if bidw_rank_`v' == 2
	/* lower losing bidder */
	by contract_id: gen deltaw01_`v' = (bidw_`v' - bidw_`v'0)/bidw_`v' if bidw_rank_`v' > 0
	by contract_id: replace deltaw01_`v' = (bidw_`v' - bidw_`v'1)/bidw_`v' if bidw_rank_`v' == 0
	
	by contract_id: gen pdeltaw_`v' = (bidw_`v' - bidw_`v'1)/est if bidw_rank_`v' > 1 
	by contract_id: replace pdeltaw_`v' = (bidw_`v' - bidw_`v'2)/est if bidw_rank_`v' == 1
	/* for placebo*/
	by contract_id: gen pdeltaw23_`v' = (bidw_`v' - bidw_`v'2)/est if bidw_rank_`v' >2
	by contract_id: replace pdeltaw23_`v' = (bidw_`v' - bidw_`v'3)/est if bidw_rank_`v' == 2
}


sort contract_id bid_first tm_bid_first
*order contract_id winner bidw_rank*first bid_rank*first deltaw*first bidw_first*
*br contract_id bid_first bid_last winner gtwinner* bidw_first bidw_last bidw_rank*
cap drop temp


/*count if bid_rank == 2
count if delta_last == 0
count if abs(delta_last) < .001
sum bid_last if delta_last == 0, d*/


gen FLAG = 0
cap drop deadline_bidding
*br deadlineforbidsubmission
gen temp = ustrregexs(1) if ustrregexm(deadlineforbidsubmission, "([0-9.]+) [0-9:]")
gen deadline_bidding = date(temp, "DMY")
format deadline_bidding %td

gen GNAT = (NAT == 1 | donor ~= "")


compress

keep if NAT == 1
rename deadline_bidding bid_date
************* backlog construction module *****************
do backlog_construction.do
backlog_construction "" ""
***********************************************************
************* fowardlog construction module ***************
do forwardlog_construction	
fowardlog_construction "" ""
***********************************************************


save ../data/georgia/sample_georgia26may22.dta, replace /*fix sample*/




*'http://tenders.procurement.gov.ge/public/?go=1741&lang=en'

